//   -------------------- iSharePoint -------------------- //
//  
//   Company:	IT Complex, LLC
//   Project:	Smart.Common
//   File name:	SqlSessionBase.cs
//   Developer:	Igor Solomatov
//   Created:	28.08.2012
//  
//   -------------------- iSharePoint -------------------- //

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Reflection;
using System.Security.Principal;
using System.Threading;

namespace Smart.SQL
{
	public class SqlSessionBase : DisposableBase
	{
		public SqlSessionBase(SqlSettings settings)
		{
			SessionId = Guid.NewGuid();
			Settings = settings;
			Connection = null;
		}

		public Guid SessionId { get; set; }
		public SqlSettings Settings { get; set; }
		protected SqlConnection Connection { get; set; }
		protected SqlTransaction Transaction { get; set; }
		protected SqlCommand Command { get; set; }

		#region IDisposable Members

		/// <summary>
		/// Dispose managed resources. Method called only once.
		/// </summary>
		protected override void DisposeManaged()
		{
			if (Command != null) CloseCommand();
			if (Transaction != null) CommitTran();
			if (Connection != null) Disconnect();
		}

		/// <summary>
		/// Dispose unmanaged resources. Method called only once.
		/// </summary>
		protected override void DisposeUnmanaged()
		{
		}

		#endregion

		public virtual string GetConnectionString()
		{
			var scsb =
				new SqlConnectionStringBuilder
					{
						DataSource = Settings.SqlServerMain,
						FailoverPartner = Settings.SqlServerFailover ?? "",
						InitialCatalog = Settings.DatabaseName,
						IntegratedSecurity = true,
						ApplicationName = "SmartLib", //." + SessionId.GetHashCodeX(),
						ConnectTimeout = Configuration.ConnectionTimeout,
						Pooling = true,
					};

			return scsb.ToString();
		}

		protected int GetConnectionKey()
		{
			return Settings.GetKey();
		}

		[ThreadStatic]
		public static int ActiveConnections;

		[ThreadStatic]
		public static long TotalConnections = 0;

		protected bool Connect()
		{
			//H.LT("> Connect");

			Connection = null;

			var sw = Stopwatch.StartNew();

			var attempts = 0;

			SqlConnection connection = null;
			for (int i = 0, iMax = Configuration.ConnectionAttempts; i < iMax && connection == null; i++)
			{
				attempts++;

				connection = new SqlConnection(GetConnectionString());

				try
				{
					using (var ctx = WindowsIdentity.Impersonate(IntPtr.Zero))
					{
						try
						{
							connection.Open();
						}
						finally
						{
							ctx.Undo();
						}
					}

					ActiveConnections++;

					Interlocked.Increment(ref TotalConnections);
				}
				catch (Exception x)
				{
					H.LX("SqlSessionBase.Connect", x);

					connection = null;
				}

				if (connection == null)
					Thread.Sleep(Configuration.ConnectionAttemptTimeout);
			}

			sw.Stop();

			Connection = connection;

			if (sw.ElapsedMilliseconds > 50)
				H.LT("SqlSessionBase.Connect: long {0}th connection open, {1} attempts, in total {2} ms, overall {3} connections", ActiveConnections, attempts, sw.ElapsedMilliseconds, TotalConnections);

			return Connection != null;
		}

		protected bool IsOpened()
		{
			return
				Connection != null && (Connection.State == ConnectionState.Open);
		}

		protected bool IsRunning()
		{
			return
				Connection != null &&
				(Connection.State == ConnectionState.Connecting ||
				 Connection.State == ConnectionState.Executing ||
				 Connection.State == ConnectionState.Fetching);
		}

		protected bool Disconnect()
		{
			//H.LT("< Disconnect");

			if (Connection == null || (!IsOpened() && !IsRunning()))
				return false;

			try
			{
				Connection.Close();

				ActiveConnections--;
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.Disconnect", x);

				Connection = null;

				return false;
			}

			Connection = null;

			return true;
		}

		public bool BeginTran(IsolationLevel level)
		{
			EnsureConnection();

			//H.LT(">   BeginTran");

			try
			{
				Transaction = Connection.BeginTransaction(level);
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.BeginTran", x);

				Transaction = null;

				return false;
			}

			return true;
		}

		public bool UnderTransaction()
		{
			return Transaction != null;
		}

		public bool CommitTran()
		{
			//H.LT("<   CommitTran");

			if (Transaction == null)
				return false;

			try
			{
				Transaction.Commit();
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.CommitTran", x);

				Transaction = null;

				return false;
			}

			Transaction = null;

			return true;
		}

		public bool RollbackTran()
		{
			//H.LT("<   RollbackTran");

			if (Transaction == null)
				return false;

			try
			{
				Transaction.Rollback();
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.RollbackTran", x);

				Transaction = null;

				return false;
			}

			Transaction = null;

			return true;
		}

		protected bool CreateCommand()
		{
			//H.LT(">     CreateCommand");

			if (Command != null)
				return false;

			try
			{
				Command = Connection.CreateCommand();

				if (Transaction != null)
					Command.Transaction = Transaction;
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.CreateCommand", x);

				Command = null;

				return false;
			}

			return true;
		}

		protected bool CloseCommand()
		{
			//H.LT("<     CloseCommand");

			if (Command == null)
				return false;

			try
			{
				Command.Dispose();
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.CloseCommand", x);

				Command = null;

				return false;
			}

			Command = null;

			return true;
		}

		public SqlConnection GetConnection()
		{
			EnsureConnection();

			return Connection;
		}

#if DEBUG
		private int _threadId = -1;

		protected void EnsureConnection()
		{
			if (_threadId < 0)
				_threadId = Thread.CurrentThread.ManagedThreadId;
			else if (_threadId != Thread.CurrentThread.ManagedThreadId)
				throw new InvalidOperationException("SqlSessionBase can't be used in MT mode!");

			if (IsRunning())
				throw new InvalidOperationException("SqlSessionBase is already used!");

			if (!IsOpened() && !Connect())
				throw new InvalidOperationException("SqlSessionBase can't connect!");
		}
#else
		protected void EnsureConnection()
		{
			if (IsRunning())
				throw new InvalidOperationException("SqlSessionBase is already used!");

			if (!IsOpened() && !Connect())
				throw new InvalidOperationException("SqlSessionBase can't connect!");
		}
#endif

		protected void InitCommand(CommandType commandType, string text, params SqlParameter[] parameters)
		{
			EnsureConnection();

			if (!CreateCommand())
				throw new InvalidOperationException("SqlSessionBase can't create command!");

			Command.CommandType = commandType;
			Command.CommandText = text;
			Command.CommandTimeout = Configuration.CommandTimeout;

			if (parameters == null) return;

			foreach (var parameter in parameters)
				Command.Parameters.Add(parameter);
		}

		public int ExecuteNonQuery(CommandType commandType, string text, params SqlParameter[] parameters)
		{
			InitCommand(commandType, text, parameters);

			try
			{
				return Command.ExecuteNonQuery();
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.ExecuteNonQuery", "ExecuteNonQuery text: " + text, x);
				throw new TargetInvocationException(x);
			}
			finally
			{
				CloseCommand();
			}
		}

		public object ExecuteScalar(CommandType commandType, string text, params SqlParameter[] parameters)
		{
			InitCommand(commandType, text, parameters);

			try
			{
				return Command.ExecuteScalar();
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.ExecuteScalar", "ExecuteScalar text: " + text, x);
				throw new TargetInvocationException(x);
			}
			finally
			{
				CloseCommand();
			}
		}

		public bool ExecuteReader(Func<SqlDataReader, bool> handler, CommandBehavior behavior, CommandType commandType, string text, params SqlParameter[] parameters)
		{
			InitCommand(commandType, text, parameters);

			try
			{
				//H.LT(">       Reader");

				using (var reader = Command.ExecuteReader(behavior))
				{
					while (reader.Read())
					{
						if (!handler(reader))
							break;
					}
				}

				//H.LT("<       Reader");

				return true;
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.ExecuteReader", "ExecuteReader text: " + text, x);

				return false;
			}
			finally
			{
				CloseCommand();
			}
		}

		public DataTable ExecuteReaderDataTable(CommandBehavior behavior, CommandType commandType, string text, params SqlParameter[] parameters)
		{
			InitCommand(commandType, text, parameters);

			try
			{
				//H.LT(">       Reader");

				var data = new DataTable();

				using (var reader = Command.ExecuteReader(behavior))
				{
					data.Load(reader);
				}

				//H.LT("<       Reader");

				return data;
			}
			catch (Exception x)
			{
				H.LX("SqlSessionBase.ExecuteReader", "ExecuteReader text: " + text, x);

				return null;
			}
			finally
			{
				CloseCommand();
			}
		}

		public Dictionary<string, object> ExecuteReaderRow(CommandType commandType, string text, string[] columns, params SqlParameter[] parameters)
		{
			Dictionary<string, object> values = null;

			ExecuteReader(
				reader =>
				{
					values = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);

					if (columns != null)
					{
						for (int i = 0, iMax = columns.Length; i < iMax; i++)
							values[columns[i]] = reader[i].PatchFromSql();
					}
					else
					{
						for (int i = 0, iMax = reader.FieldCount; i < iMax; i++)
							values[reader.GetName(i)] = reader[i].PatchFromSql();
					}

					return false;
				},
				CommandBehavior.SingleResult | CommandBehavior.SequentialAccess | CommandBehavior.SingleRow,
				commandType,
				text,
				parameters);

			return values;
		}

		public SqlDependency CreateDependency(CommandType commandType, string text, int timeout, params SqlParameter[] parameters)
		{
			InitCommand(commandType, text, parameters);

			Command.Notification = null;

			return new SqlDependency(Command, null, timeout);
		}

		public SqlDataReader ExecuteDependecy()
		{
			if (Command == null)
				throw new InvalidOperationException();

			return Command.ExecuteReader();
		}

		public void CloseDependency(SqlDataReader dependency)
		{
			if (dependency != null)
				dependency.Dispose();

			CloseCommand();
		}

		public bool BulkCopy(string tableName, DataTable source)
		{
			EnsureConnection();

			using (var bulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, Transaction))
			{
				bulkCopy.DestinationTableName = "[" + tableName + "]";

				var mappings = bulkCopy.ColumnMappings;

				foreach (DataColumn column in source.Columns)
					mappings.Add(column.ColumnName, column.ColumnName);

				try
				{
					bulkCopy.WriteToServer(source);
				}
				catch (Exception x)
				{
					H.LX("SqlSessionBase.BulkCopy", "BulkCopy tableName: " + tableName, x);

					return false;
				}
			}

			return true;
		}

		#region Nested type: Configuration

		/// <summary>
		/// Configuration values (never changed)
		/// </summary>
		public static class Configuration
		{
			public const int ConnectionTimeout = 60;
			public const int CommandTimeout = 300;
			public const int ConnectionAttempts = 3;
			public const int ConnectionAttemptTimeout = 1000;

			static Configuration()
			{
			}
		}

		#endregion
	}
}